﻿import datetime
import xlrd
import sqlite3
import xlwt
import re
from optparse import OptionParser
import os
import sys

class MyTime(object):
    def __init__(self, h, m,s):
        self.h, self.m ,self.s= int(h), int(m),int(s)

    def __repr__(self):
        return "(%d:%d:%d)" % (self.h, self.m,self.s)
    
    
def adapt_time(tm):
    return "(%d:%d:%d)" %(tm.h, tm.m,tm.s)

def convert_time(s):
    #print 'convert time',s
    pattern = re.compile('\((\d{1,})\:(\d{1,})\:(\d{1,})\)')
    result = pattern.findall(s)
    #print result
    (h,m,s) = result[0]
    #h,m,s = map(int, s.split(":"))
    return MyTime(h,m,s)



class RecordModel(object):
    def __init__(self):
        # Register the adapter
        sqlite3.register_adapter(MyTime, adapt_time)

        # Register the converter
        sqlite3.register_converter("mytime", convert_time)
        self.conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
        self.cursor = self.conn.cursor()
        self.cursor.execute(' CREATE TABLE recordtable(id integer NOT NULL PRIMARY KEY,\
        name varchar(50),\
        coreid varchar(50),\
        depart varchar(20),\
        login_date date,\
        login_time mytime,\
        logout_time mytime)');
        self.conn.commit()
        
    def execute(self,excel_name):
        
        
        book = xlrd.open_workbook(excel_name)        
        sh = book.sheet_by_index(0)
        #print sh.nrows
        for irow in range(1,sh.nrows ):
            name = sh.cell_value(irow,0)
            coreid = sh.cell_value(irow,1)
            depart = sh.cell_value(irow,2)
            dt = sh.cell_value(irow,3)
            t = xlrd.xldate_as_tuple(dt,0)
            dt = datetime.date(t[0],t[1],t[2])            
            tm = MyTime(t[3],t[4],t[5])
            
            rec = (None,name,coreid,depart,dt,tm,tm)
            #print 'rec'
            query_list = self.cursor.execute('select * from recordtable where coreid=? and login_date=?',(coreid,dt))
            
            cnt = 0
            for item in query_list:
                cnt = cnt + 1
                dbid = item[0]
            
            if cnt == 0:
                #insert the values
                #print 'insert the value',rec
                self.cursor.execute('insert into recordtable values (?,?,?,?,?,?,?)', rec)

            else:
                #print dbid
                self.cursor.execute('update recordtable set logout_time = ? where id = ?',(tm,dbid))
            self.conn.commit()         
            
            #print "the rec is ",rec
        
    def output(self,filename):
        
        style_login_date = xlwt.easyxf(num_format_str=u'YY-MM-DD')
        style_login_time = xlwt.easyxf(num_format_str=u'H:MM')
        style_duration_time = xlwt.easyxf(num_format_str='0.0')
        wb = xlwt.Workbook()
        ws = wb.add_sheet('logindate')

        query_list = self.cursor.execute('select * from recordtable')
        
        row = 0
        ws.write(row, 0, u'日期')
        ws.write(row, 1, u'姓名')
        ws.write(row, 2, u'员工号')
        ws.write(row, 3, u'部门')
        ws.write(row, 4, u'签到时间')
        ws.write(row, 5, u'签退时间')
        ws.write(row, 6, u'当日出勤小时')
        ws.write(row, 7, u'备注')
        row = row + 1
        for each_row in query_list:            
            ws.write(row, 0, each_row[4],style_login_date)
            ws.write(row, 1, '%s'%(each_row[1]))
            ws.write(row, 2, '%s'%(each_row[2]))
            ws.write(row, 3, '%s'%(each_row[3]))
            tm = datetime.time(each_row[5].h,each_row[5].m,each_row[5].s)
            ws.write(row, 4, tm,style_login_time)
            tm = datetime.time(each_row[6].h,each_row[6].m,each_row[6].s)
            ws.write(row, 5, tm,style_login_time)
            formula_str = "(F%d-E%d)*24"%(row + 1,row + 1)
            ws.write(row, 6, xlwt.Formula(formula_str),style_duration_time)

            row = row + 1
        
          

        wb.save(filename)

    def close(self):
        self.cursor.close()
        self.conn.close()           


def main(*argv):
    """RecordConvert Main Funcion """
    if argv:
        argv = list(argv)
    else:
        argv = sys.argv[1:]    
    
    usage = "usage: python recordconvert -i [prod] -o [opl_product]"    
    parser = OptionParser(usage=usage)    
    parser.add_option("-i", "--input", dest="inputfile",type="string",help="The output file")
    parser.add_option("-o", "--output", dest="outputfile",type="string",help="The output file")
    
    (options, args) = parser.parse_args(args=argv)
    
    outputfile = os.path.abspath(options.outputfile)    
    inputfile = os.path.abspath(options.inputfile)   

    rd = RecordModel()
    rd.execute(inputfile)
    rd.output(outputfile)

if __name__ == "__main__":
    main()
        
